package com.mycookbook;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * @author S�ren Andersen
 * 
 */
public class DatabaseHandler extends SQLiteOpenHelper {

	// All Static variables
	// Database Version
	private static final int DATABASE_VERSION = 1;

	// Database Name
	private static final String DATABASE_NAME = "recipeDatabase";

	// Recipes table name
	private static final String TABLE_RECIPES = "recipes";

	// Recipes Table Columns names
	private static final String KEY_ID = "id";
	private static final String KEY_RECIPENAME = "name";
	private static final String KEY_CATEGORY = "category";
	private static final String KEY_APPROACH = "approach";
	private static final String KEY_INGREDIENTS = "ingredients";
	private static final String KEY_IMAGEPATH = "imagepath";

	public DatabaseHandler(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	// Creating Tables
	@Override
	public void onCreate(SQLiteDatabase db) {

		String CREATE_RECIPES_TABLE = "CREATE TABLE " + TABLE_RECIPES + " ("
				+ KEY_ID + " INTEGER PRIMARY KEY, " + KEY_RECIPENAME
				+ " TEXT, " + KEY_CATEGORY + " TEXT, " + KEY_APPROACH
				+ " TEXT, " + KEY_INGREDIENTS + " TEXT, " + KEY_IMAGEPATH
				+ " TEXT" + ")";

		String log = CREATE_RECIPES_TABLE;
		// Writing Contacts to log
		Log.d("sql create statement ", log);
		db.execSQL(CREATE_RECIPES_TABLE);
	}

	// Upgrading database
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// Drop older table if existed
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_RECIPES);

		// Create tables again
		onCreate(db);
	}

	public void resetDB(SQLiteDatabase db) {
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_RECIPES);

		// Create tables again
		onCreate(db);
	}

	// Adding new Recipe
	public void addRecipe(Recipe recipe) {

		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(KEY_RECIPENAME, recipe.getName());
		values.put(KEY_CATEGORY, recipe.getCategory());
		values.put(KEY_APPROACH, recipe.getApproach());
		values.put(KEY_INGREDIENTS, recipe.getIngredients());
		values.put(KEY_IMAGEPATH, recipe.getImagePath());

		// Inserting Row
		db.insert(TABLE_RECIPES, null, values);

		db.close();
	}

	// Getting single Recipe
	public Recipe getRecipe(int id) {
		SQLiteDatabase db = this.getWritableDatabase();

		Cursor cursor = db.query(TABLE_RECIPES, new String[] { KEY_ID,
				KEY_RECIPENAME, KEY_CATEGORY, KEY_APPROACH, KEY_INGREDIENTS,
				KEY_IMAGEPATH }, KEY_ID + "=?",
				new String[] { String.valueOf(id) }, null, null, null, null);
		if (cursor != null)
			cursor.moveToFirst();

		Recipe recipe = new Recipe(Integer.parseInt(cursor.getString(0)),
				cursor.getString(1), cursor.getString(2), cursor.getString(3),
				cursor.getString(4), cursor.getString(5));
		// return recipe
		return recipe;
	}

	public Recipe getRecipeByName(String name) {
		SQLiteDatabase db = this.getWritableDatabase();

		Cursor cursor = db.query(TABLE_RECIPES, new String[] { KEY_ID,
				KEY_RECIPENAME, KEY_CATEGORY, KEY_APPROACH, KEY_INGREDIENTS,
				KEY_IMAGEPATH }, KEY_RECIPENAME + "=?",
				new String[] { String.valueOf(name) }, null, null, null, null);
		if (cursor != null)
			cursor.moveToFirst();

		Recipe recipe = new Recipe(Integer.parseInt(cursor.getString(0)),
				cursor.getString(1), cursor.getString(2), cursor.getString(3),
				cursor.getString(4), cursor.getString(5));
		// return recipe
		return recipe;
	}

	// Getting All Recipes
	public List<Recipe> getAllRecipes() {
		List<Recipe> recipeList = new ArrayList<Recipe>();
		// Select All Query
		String selectQuery = "SELECT * FROM " + TABLE_RECIPES;

		SQLiteDatabase db = this.getWritableDatabase();

		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				Recipe recipe = new Recipe();
				recipe.setID(Integer.parseInt(cursor.getString(0)));
				recipe.setName(cursor.getString(1));
				recipe.setCategory(cursor.getString(2));
				recipe.setApproch(cursor.getString(3));
				recipe.setIngredients(cursor.getString(4));
				recipe.setImagePath(cursor.getString(5));
				// Adding recipe to list
				recipeList.add(recipe);
			} while (cursor.moveToNext());
		}

		// return recipe list
		return recipeList;
	}

	/**
	 * This method will clear the database so use it with care, this cannot be
	 * undone.
	 */
	public void deleteAllRecipies() {

		String DeleteQuery = "DELETE * FROM " + TABLE_RECIPES;

		SQLiteDatabase db = this.getWritableDatabase();

		db.rawQuery(DeleteQuery, null);

	}

	public List<Recipe> getRecipesByCategory(String category) {
		List<Recipe> recipeList = new ArrayList<Recipe>();
		// Select All Query
		String selectRecipesByCategoryQuery = "SELECT * FROM " + TABLE_RECIPES
				+ " WHERE " + KEY_CATEGORY + "= '" + category + "'";

		SQLiteDatabase db = this.getWritableDatabase();

		Cursor cursor = db.rawQuery(selectRecipesByCategoryQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				Recipe recipe = new Recipe();
				recipe.setID(Integer.parseInt(cursor.getString(0)));
				recipe.setName(cursor.getString(1));
				recipe.setCategory(cursor.getString(2));
				recipe.setApproch(cursor.getString(3));
				recipe.setIngredients(cursor.getString(4));
				recipe.setImagePath(cursor.getString(5));
				// Adding recipe to list
				recipeList.add(recipe);
			} while (cursor.moveToNext());
		}

		// return recipe list
		return recipeList;
	}

	// Getting Recipe Count
	public int getRecipesCount() {
		String countQuery = "SELECT * FROM " + TABLE_RECIPES;

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(countQuery, null);
		cursor.close();

		// return count
		return cursor.getCount();
	}

	// Updating single Recipe
	public int updateRecipe(Recipe recipe) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(KEY_RECIPENAME, recipe.getName());
		values.put(KEY_CATEGORY, recipe.getCategory());
		values.put(KEY_APPROACH, recipe.getApproach());
		values.put(KEY_INGREDIENTS, recipe.getIngredients());
		values.put(KEY_IMAGEPATH, recipe.getImagePath());

		// updating row
		return db.update(TABLE_RECIPES, values, KEY_ID + " =?",
				new String[] { String.valueOf(recipe.getID()) });
	}

	// Deleting single Recipe
	public void deleteRecipe(Recipe recipe) {
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(TABLE_RECIPES, KEY_ID + " = ?",
				new String[] { String.valueOf(recipe.getID()) });
		close();
	}
	
	public String getImageUrl(String name){
		SQLiteDatabase db = this.getWritableDatabase();

		Cursor cursor = db.query(TABLE_RECIPES, new String[] { KEY_ID,
				KEY_RECIPENAME, KEY_CATEGORY, KEY_APPROACH, KEY_INGREDIENTS,
				KEY_IMAGEPATH }, KEY_RECIPENAME + "=?",
				new String[] { String.valueOf(name) }, null, null, null, null);
		if (cursor != null)
			cursor.moveToFirst();

		String imageURL = cursor.getString(5);

		return imageURL;
	}

	/**
	 * @param name
	 *            , name of recipe
	 * @return a String array of Ingredients
	 */
	public String[] getIngredientsAsArray(String name) {
		SQLiteDatabase db = this.getWritableDatabase();

		Cursor cursor = db.query(TABLE_RECIPES, new String[] { KEY_ID,
				KEY_RECIPENAME, KEY_CATEGORY, KEY_APPROACH, KEY_INGREDIENTS,
				KEY_IMAGEPATH }, KEY_RECIPENAME + "=?",
				new String[] { String.valueOf(name) }, null, null, null, null);
		if (cursor != null)
			cursor.moveToFirst();

		String[] Ingredients = cursor.getString(4).split(";");

		return Ingredients;

	}

	/**
	 * @param name
	 *            , name of recipe
	 * @return a String array of Approach steps
	 */
	public String[] getApproachAsArray(String name) {
		SQLiteDatabase db = this.getWritableDatabase();

		Cursor cursor = db.query(TABLE_RECIPES, new String[] { KEY_ID,
				KEY_RECIPENAME, KEY_CATEGORY, KEY_APPROACH, KEY_INGREDIENTS,
				KEY_IMAGEPATH }, KEY_RECIPENAME + "=?",
				new String[] { String.valueOf(name) }, null, null, null, null);
		if (cursor != null)
			cursor.moveToFirst();
		
		String[] Approach = cursor.getString(3).split(";");

		return Approach;

	}
	
	public String getApproach(String name) {
		SQLiteDatabase db = this.getWritableDatabase();

		Cursor cursor = db.query(TABLE_RECIPES, new String[] { KEY_ID,
				KEY_RECIPENAME, KEY_CATEGORY, KEY_APPROACH, KEY_INGREDIENTS,
				KEY_IMAGEPATH }, KEY_RECIPENAME + "=?",
				new String[] { String.valueOf(name) }, null, null, null, null);
		if (cursor != null)
			cursor.moveToFirst();
		
		String Approach = cursor.getString(3);

		return Approach;

	}
	/**
	 * @param name
	 *            , the name of the recipe as string
	 * @param imagePath
	 *            , the new image path as string
	 * @return
	 */
	public int updateImageUrl(String name, String imagePath) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(KEY_IMAGEPATH, imagePath);

		// updating row
		return db.update(TABLE_RECIPES, values, KEY_RECIPENAME + " = ?",
				new String[] { String.valueOf(name) });

	}

	/**
	 * @param name
	 *            , name of the recipe as string
	 * @param Ingredients
	 *            , all the ingredients in a string seperated by ;
	 * @return
	 */
	public int updateIngredients(String name, String Ingredients) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(KEY_INGREDIENTS, Ingredients);

		// updating row
		return db.update(TABLE_RECIPES, values, KEY_RECIPENAME + " = ?",
				new String[] { String.valueOf(name) });

	}

	/**
	 * @param name
	 *            , recipe name as string
	 * @param Approach
	 *            , All approach steps in a single string seperated by ;
	 * @return
	 */
	public int updateApproach(String name, String Approach) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(KEY_APPROACH, Approach);

		// updating row
		return db.update(TABLE_RECIPES, values, KEY_RECIPENAME + " = ?",
				new String[] { String.valueOf(name) });

	}

}
